package com.yangqun.main;

import com.yangqun.Studer;
import com.yangqun.annotation.ExcelBeanMethodUtil;
import com.yangqun.util.ReflexUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;

public class ExportExcelUtil<T> {
	//测试
	public static void main(String[] args) throws Exception {
		//构建数据
		List<Studer> list = new ArrayList<Studer>();
		for(int i = 0; i < 10; i++){
			Studer studer = new Studer();
			studer.setName("name" + i);
			studer.setBanji("四年级一班");
			studer.setBishicj((int)(Math.random() * 100 + 1));
			studer.setJishicj((int)(Math.random() * 100 + 1));
			list.add(studer);
		}
		//根据数据生成文件
		ExportExcelUtil<Studer> excelManage = new ExportExcelUtil();
		excelManage.genExcel(list,"D://workbook.xls");
	}
	/**
	 * YangTao 2017年4月20日 15:20:25
	 * @Title: genExcel
	 * @Description: 生成Excel，该放法简单，但是需要实体类提供相应的支持。有重载方法可使用
	 * @param @param beans
	 * @param @param filePath
	 * @param @throws IllegalAccessException
	 * @param @throws IllegalArgumentException
	 * @param @throws InvocationTargetException
	 * @param @throws IOException
	 * @return void
	 */
	public void genExcel(List<T> beans, final String filePath) throws IllegalArgumentException{
		genExcel(beans, "", filePath);
	}

	public void genExcel(List<T> beans,String title, final String filePath) throws  IllegalArgumentException{
		if(beans != null && beans.size() > 0){
			T studer = beans.get(0);
			ReflexUtil<T> reflexUtil = new ReflexUtil<T>();
			String[] head =  reflexUtil.getExcelHeadByBean(studer);
			genExcel(beans, title ,head,filePath);
		}else{
			//没有数据
			return;
		}
	}

	public void genExcel(List<T> beans, String[] head,final String filePath) throws IllegalArgumentException{
		genExcel(beans,null,head,filePath);
	}

	public void genExcel(Workbook wb, List<T> beans, String title , String[] head, final String filePath,final String sheetName) throws  IllegalArgumentException{
		genExcel(wb,beans,title,head,filePath,sheetName,null);
	}

	public void genExcel(List<T> beans, String title , String[] head, final String filePath,final String sheetName) throws  IllegalArgumentException{
		genExcel(null,beans,title,head,filePath,sheetName);
	}

	public void genExcel(List<T> beans, String title , String[] head, final String filePath) throws  IllegalArgumentException {
		genExcel(beans,title,head,filePath,null);
	}

	public void genExcel(Workbook wb, List<T> beans, String title , String[] head, final String filePath, final String sheetName, Map<String,String> summary) throws  IllegalArgumentException {

		if(beans == null || beans.size() == 0){
			//没得数据还导个毛啊
			return;
		}
		int rowIndex = 0;
		// 创建HSSFWorkbook对象(excel的文档对象)
		if(wb == null){
//			if(filePath.toLowerCase().endsWith("xlsx")){
//				wb = new XSSFWorkbook();
//			}else if(filePath.toLowerCase().endsWith("xls")){
//				wb = new HSSFWorkbook();
//			}
			wb = new HSSFWorkbook();

		}
		// 建立新的sheet对象（excel的表单）
		Sheet sheet;
		if(sheetName == null || sheetName.equals("")){
			sheet = wb.createSheet();
		}else {
			sheet = wb.createSheet(sheetName);
		}

		CellStyle style = wb.createCellStyle();
		//设置边框
		style.setBorderBottom(BorderStyle.THIN); //下边框
		style.setBorderLeft(BorderStyle.THIN);//左边框
		style.setBorderTop(BorderStyle.THIN);//上边框
		style.setBorderRight(BorderStyle.THIN);//右边框
		// 设置这些样式
		//水平居中
		style.setAlignment(HorizontalAlignment.CENTER);
		//垂直居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		// 背景色
//        style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_GREEN.getIndex());

		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        HSSFFont font = wb.createFont();
//        font.setFontName("宋体");
//        // 设置字体大小
//        style.setFont(font);

		if(title == null || title.equals("")){
		}else{
			// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
			Row row1 = sheet.createRow(rowIndex);
			row1.setHeight((short)450);
			// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
			Cell cell = row1.createCell(0);
//            row1.setHeightInPoints((short) (15.625*2));
			// 设置字体大小
//            font.setFontHeightInPoints((short)12);
//            style.setFont(font);
			cell.setCellStyle(style);
			// 设置单元格内容
			cell.setCellValue(title);
			for(int i = 1; i < head.length;i++){
				Cell c = row1.createCell(i);
				c.setCellStyle(style);
			}
			// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
			CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, head.length - 1);
			sheet.addMergedRegion(cellRangeAddress);

			++rowIndex;
		}

		if(summary != null && summary.size() > 0){
			int cellNumber = 0;
			Iterator<Map.Entry<String,String>> iterator = summary.entrySet().iterator();

			while(iterator.hasNext()){
				Map.Entry<String,String> entry = iterator.next();

				Row rowSummary = sheet.createRow(rowIndex);
				rowSummary.setHeight((short)450);
				String key = entry.getKey();
				String value = entry.getValue();
				Cell cellKey = rowSummary.createCell(cellNumber);
				Cell cellValue = rowSummary.createCell(cellNumber+1);
				cellKey.setCellValue(key);
				cellValue.setCellValue(value);
				rowIndex++;
			}
		}

		Row row2 = sheet.createRow(rowIndex);
		//行高
		row2.setHeight((short)450);
		// 在sheet里创建第二行
		//生成表头内容
		for(int i = 0; i < head.length; i++){
			//列宽
			sheet.setColumnWidth(i, 12 * 450);
			// 创建单元格并设置单元格内容
			Cell cell1 = row2.createCell(i);
			cell1.setCellStyle(style);
			cell1.setCellValue(head[i]);
		}
		++rowIndex;

		ReflexUtil<T> reflexUtil = new ReflexUtil<T>();
		Map<String,Method> headMethodMap = new HashMap<String,Method>();
		Method[] methods = beans.get(0).getClass().getMethods();
		Field[] fields = beans.get(0).getClass().getFields();

		//生成Excel内容
		for(int i = 0; i < beans.size(); i++){
			Row row3 = sheet.createRow(rowIndex++);
			//行高
			row3.setHeight((short)400);
			T studer = beans.get(i);

			for(int j = 0; j < head.length; j++){
				//获取Excel表头
				String headName = head[j];
				Method method = headMethodMap.get(headName);
				if(method == null){
					//根据表头获取方法名称
					String methodName = reflexUtil.getMethodName(fields,headName);
					//根据方法获取方法名称获取方法
					method = reflexUtil.getMethodByName(methods,methodName);
					headMethodMap.put(headName, method);
				}
				if(method == null){
					continue;
				}
				Cell cellT = row3.createCell(j);
				//cellT.setCellStyle(style1);
				//调用方法设置到Excel中
				setExcelCellValue(studer,cellT,method);
			}
		}
		headMethodMap.clear();

		if(filePath != null && !"".equals(filePath)) {
			//判断路径是否存在不存在创建目录
			File file = new File(filePath);
			if (!file.getParentFile().exists()) {
				file.getParentFile().mkdirs();
			}
			//如果文件不为空，则先删除文件
			if (file.isFile() && file.exists()) {
				file.delete();
			}
			//写入到文件
			writeFile(wb, filePath);
		}

	}
	
	

	
	/**
	 * YangTao 2017年4月20日 11:25:18
	* @Title: writeFile
	* @Description: (HSSFWorkbook 写入文件)
	* @param @param outputStream
	* @param @param fileName 需要文件的全路径
	* @return void
	 * @throws IOException 
	 */
	public void writeFile(Workbook wb,String fileName) {
		FileOutputStream output = null;
		try {
			output = new FileOutputStream(fileName);
			wb.write(output);
			output.flush();
			output.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			if(output != null){
				try {
					output.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	

	/**
	 * YangTao 2017年4月20日 11:24:27
	* @Title: setExcelCellValue 
	* @Description: (写入值到Excel中，因为类型不太一样 ，所有需要这么处理一下)
	* @param @param studer
	* @param @param cell
	* @param @param method
	* @return void
	 */
	private void setExcelCellValue(T studer, Cell cell,Method method){
		if(method == null){
			cell.setCellValue("");
            return;
		}
		try {
			Object value = method.invoke(studer, new Object[]{});
			if (value == null) {
                cell.setCellValue("");
                return;
            } else if (value instanceof Float) {
                cell.setCellValue(((Float) value).doubleValue());
                return;
            }else if (value instanceof Double) {
                cell.setCellValue(((Double) value).doubleValue());
                return;
            } else if (value instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) value).doubleValue());
                return;
            } else if (value instanceof Date) {
                cell.setCellValue((Date) value);
                return;
            }else if (value instanceof Calendar) {
                cell.setCellValue((Calendar) value);
                return;
            } else if (value instanceof Byte) {
                cell.setCellValue(((Byte) value).intValue());
                return;
            }else if (value instanceof Short) {
                cell.setCellValue(((Short) value).intValue());
                return;
            }else if (value instanceof Integer) {
                cell.setCellValue(((Integer) value).intValue());
                return;
            }else if (value instanceof Long) {
                cell.setCellValue(((Long) value).longValue());
                return;
            } else if (value instanceof BigInteger) {
                cell.setCellValue(((BigInteger) value).doubleValue());
                return;
            }else {
                String fixedValue = value.toString();
                if (fixedValue != null) {
                    fixedValue = fixedValue.replaceAll("\r\n", "\n");
                }
                if( fixedValue.length() == 0 ){
                    cell.setCellValue("");
                }else{
                    cell.setCellValue(fixedValue);
                }
            }
			
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		
	}



}